sdk/samples/all in on code/Visual Studio 2010/CSSqlAzurePartitioning/WebRole1/SqlAzureHelper.cs

   1 /****************************** Module Header ******************************\
   2 * Module Name:  SqlAzureHelper.cs
   3 * Project: CSSqlAzurePartitioning
   4 * Copyright (c) Microsoft Corporation.
   5 *
   6 * This file is from Microsoft SQL Azure team's blog.
   7 * http://blogs.msdn.com/b/sqlazure/archive/2010/05/17/10014011.aspx
   8 *
   9 * 1. Implements forward read only cursors for performance.
  10 * 2. Support IEnumerable and LINQ
  11 * 3. Disposes of the connection and the data reader when the result set is no longer needed.
  12 *
  13 *
  14 * This source is subject to the Microsoft Public License.
  15 * See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
  16 * All other rights reserved.
  17 *
  18 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
  19 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
  20 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
  21 \***************************************************************************/
  22
  23 using System;
  24 using System.Collections.Generic;
  25 using System.Linq;
  26 using System.Data.SqlClient;
  27 using System.Data.Common;
  28 using System.Configuration;
  29 using System.Threading;
  30
  31 namespace SQLAzure
  32 {
  33     internal delegate SqlDataReader ExecuteReaderDelegate(SqlConnection sqlConnection);
  34     internal delegate void ExecuteNonQueryDelegate(SqlConnection sqlConnection);
  35
  36     sealed class SQLAzureHelper
  37     {
  38         internal static IEnumerable<DbDataRecord> ExecuteReader(String connectionString, ExecuteReaderDelegate executeDelegate)
  39         {
  40             using (SqlConnection sqlConnection = new SqlConnection(connectionString))
  41             {
  42                 // Open the connection
  43                 sqlConnection.Open();
  44
  45                 using (SqlDataReader sqlDataReader = executeDelegate(sqlConnection))
  46                 {
  47                     foreach (DbDataRecord dbDataRecord in sqlDataReader.Cast<DbDataRecord>())
  48                         yield return dbDataRecord;
  49                 }
  50             }
  51         }
  52
  53         internal static void ExecuteNonQuery(String connectionString, ExecuteNonQueryDelegate executeDelegate)
  54         {
  55             // This is the retry loop, handling the retries session
  56             // is done in the catch for performance reasons
  57             for (Int32 attempt = 1; ; )
  58             {
  59                 try
  60                 {
  61                     using (SqlConnection sqlConnection = new SqlConnection(connectionString))
  62                     {
  63                         // Open the connection
  64                         sqlConnection.Open();
  65                         executeDelegate(sqlConnection);
  66                     }
  67                     // Success Break Out Of Attempt Loop
  68                     break;
  69                 }
  70                 catch (SqlException sqlException)
  71                 {
  72                     // Increment Trys
  73                     attempt++;
  74
  75                     // Find Maximum Trys
  76                     Int32 maxRetryCount = Int32.Parse(
  77                         ConfigurationManager.AppSettings["ConnectionRetrys"]);
  78
  79                     // Throw Error if we have reach the maximum number of retries
  80                     if (attempt == maxRetryCount)
  81                         throw;
  82
  83                     // Determine if we should retry or abort.
  84                     if (!SQLAzureHelper.RetryLitmus(sqlException))
  85                         throw;
  86                     else
  87                         Thread.Sleep(SQLAzureHelper.ConnectionRetryWaitSeconds(attempt));
  88                 }
  89             }
  90         }
  91
  92         /// <summary>
  93         /// Number of seconds to wait before retrying the connection
  94         /// </summary>
  95         /// <param name="attempt"></param>
  96         /// <returns></returns>
  97         public static Int32 ConnectionRetryWaitSeconds(Int32 attempt)
  98         {
  99             Int32 connectionRetryWaitSeconds = Int32.Parse(ConfigurationManager.
 100                 AppSettings["ConnectionRetryWaitSeconds"])
 101                 * 1000;
 102
 103             // Backoff Throttling, here we slow the retries, based on the Number of
 104             // Attempts
 105             connectionRetryWaitSeconds = connectionRetryWaitSeconds *
 106                 (Int32)Math.Pow(2, attempt);
 107
 108             return (connectionRetryWaitSeconds);
 109         }
 110
 111         /// <summary>
 112         /// Determine from the exception if the execution
 113         /// of the connection should Be attempted again
 114         /// </summary>
 115         /// <param name="exception">Generic Exception</param>
 116         /// <returns>True if a a retry is needed, false if not</returns>
 117         public static Boolean RetryLitmus(SqlException sqlException)
 118         {
 119             switch (sqlException.Number)
 120             {
 121                 // The service has encountered an error
 122                 // processing your request. Please try again.
 123                 // Error code %d.
 124                 case 40197:
 125                 // The service is currently busy. Retry
 126                 // the request after 10 seconds. Code: %d.
 127                 case 40549:
 128                 //A transport-level error has occurred when
 129                 // receiving results from the server. (provider:
 130                 // TCP Provider, error: 0 - An established connection
 131                 // was aborted by the software in your host machine.)
 132                 case 10053:
 133                     return (true);
 134             }
 135
 136             return (false);
 137         }
 138
 139         /// <summary>
 140         /// Names of the Databases In Horizontal Partition
 141         /// </summary>
 142         public static String[] ConnectionStringNames = { "Database001ConnectionString", "Database002ConnectionString" };
 143
 144         /// <summary>
 145         /// Connections Strings In the Horizontal Partition
 146         /// </summary>
 147         /// <returns></returns>
 148         public static IEnumerable<String> ConnectionStrings()
 149         {
 150             foreach (String connectionStringName in ConnectionStringNames)
 151                 yield return ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
 152         }
 153
 154         /// <summary>
 155         /// Return the Index to the Database For the Primary Key
 156         /// </summary>
 157         /// <param name="primaryKey"></param>
 158         /// <returns></returns>
 159         private static int DatabaseIndex(Guid primaryKey)
 160         {
 161             uint hash = 0;
 162
 163             foreach (byte b in primaryKey.ToByteArray())
 164             {
 165                 hash += b;
 166                 hash += (hash << 10);
 167                 hash ^= (hash >> 6);
 168             }
 169
 170             // Final avalanche
 171             hash += (hash << 3);
 172             hash ^= (hash >> 11);
 173             hash += (hash << 15);
 174
 175             return ((int)(hash % 100000000));
 176         }
 177
 178         /// <summary>
 179         /// Returns the Connection String Name for the Primary Key
 180         /// </summary>
 181         /// <param name="primaryKey"></param>
 182         /// <returns></returns>
 183         private static String ConnectionStringName(Guid primaryKey)
 184         {
 185             return (ConnectionStringNames[DatabaseIndex(primaryKey) % ConnectionStringNames.Length]);
 186         }
 187
 188         /// <summary>
 189         /// Returns the Connection String For the Primary Key
 190         /// </summary>
 191         /// <param name="primaryKey"></param>
 192         /// <returns></returns>
 193         public static String ConnectionString(Guid primaryKey)
 194         {
 195             return (ConfigurationManager.ConnectionStrings[ConnectionStringName(primaryKey)].ConnectionString);
 196         }
 197     }
 198 }